package com.etc.dao.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.rowset.CachedRowSet;

import com.etc.dao.UserDao;
import com.etc.entity.User;
import com.etc.util.DBUtil;
import com.etc.util.MD5Utile;
import com.etc.util.PageData;

/**
 * UserDaoImpl
 * @author wade
 *
 */
public class UserDaoImpl implements UserDao{
	/**
	 * 查找所有用户
	 */
	@Override
	public List<User> queryAllUser() {
		List<User> list =new ArrayList<User>();
		String sql = "select * from users";
		CachedRowSet crs = DBUtil.execQuery(sql);
		try {
			while(crs.next()) {
				int u_id=crs.getInt(1);
				String u_name=crs.getString(2);
				String u_password=crs.getString(3);
				String u_tel=crs.getString(4);
				String u_sex=crs.getString(5);
				int u_level=crs.getInt(6);
				int u_status=crs.getInt(7);
				String u_img=crs.getString(8);
				list.add(new User(u_id, u_name, u_password, u_tel, u_sex, u_level, u_status, u_img));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	/**
	 * 添加用户
	 */
	@Override
	public boolean addUser(User user) {
		String u_passwordMD5 = MD5Utile.EncodeByMD5(user.getU_password());
		if(user!=null) {
			String sql="insert into users value(null,?,?,?,?,?,?,?)";
			return DBUtil.executeUpdate(sql,user.getU_name(),u_passwordMD5,user.getU_tel(),user.getU_sex(),user.getU_level(),user.getU_status(),user.getU_img());
		}
		return false;
	}

	/**
	 * 用户注册
	 */
	@Override
	public boolean register(String u_name, String u_password,String u_tel,String u_sex,String u_img) {
		if(!("".equals(u_name))&&!("".equals(u_password))) {
			String sql="insert into users value(null,?,?,?,?,1,1,?)";
			return DBUtil.executeUpdate(sql, u_name,u_password,u_tel,u_sex,u_img);
		}
		return false;
	}
	/**
	 * 模糊查询+分页查询
	 * @param articleLike
	 * @return
	 */
	@Override
	public PageData queryUserByLikeAndLimit(int pageNo, int pageSize, String titleLike) {
		List<User> list =new ArrayList<User>();
		String sql = "select * from users where u_name like ? limit ?,?";
		CachedRowSet crs = DBUtil.execQuery(sql,"%"+titleLike+"%",(pageNo-1)*pageSize,pageSize);
		try {
			while(crs.next()) {
				int u_id=crs.getInt(1);
				String u_name=crs.getString(2);
				String u_password=crs.getString(3);
				String u_tel=crs.getString(4);
				String u_sex=crs.getString(5);
				int u_level=crs.getInt(6);
				int u_status=crs.getInt(7);
				String u_img=crs.getString(8);
				list.add(new User(u_id, u_name, u_password, u_tel, u_sex, u_level, u_status, u_img));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		int totalCount=0;
		String sql2="select count(*) from users where u_name like ?";
		CachedRowSet crs2 = DBUtil.execQuery(sql2,"%"+titleLike+"%");
		try {
			while(crs2.next()) {
				totalCount=crs2.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		PageData pageData = new PageData(list, pageNo, pageSize, totalCount);
		return pageData;
	}
	/**
	 * 修改用户
	 */
	@Override
	public boolean updateUser(User user) {
		if(user!=null) {
			String sql="update users set u_name=?,u_password=?,u_tel=?,u_sex=?,u_level=?,u_status=? where u_id=?";
			return DBUtil.executeUpdate(sql,user.getU_name(),user.getU_password(),user.getU_tel(),user.getU_sex(),user.getU_level(),user.getU_status(),user.getU_id());
		}
		return false;
	}
	/**
	 * 删除用户(数据库不删除只修改他的状态改为禁用)
	 * @param user
	 * @return
	 */
	@Override
	public boolean deleteUser(int u_id) {
		if(u_id!=0) {
			String sql="update users set u_status=0 where u_id=?";
			return DBUtil.executeUpdate(sql,u_id);
		}
		return false;
	}
	@Override
	public boolean frontUpdateUser(User user) {
		String u_passwordMD5 = MD5Utile.EncodeByMD5(user.getU_password());
		if(user!=null) {
			String sql="update users set u_name=?,u_password=?,u_tel=?,u_sex=? where u_id=?";
			return DBUtil.executeUpdate(sql,user.getU_name(),u_passwordMD5,user.getU_tel(),user.getU_sex(),user.getU_id());
		}
		return false;
	}


	
}
